
package net.biaji.android.alephclient.data;

import java.util.ArrayList;
import java.util.Date;

import net.biaji.alephclient.Book;
import net.biaji.android.alephclient.data.DataContract.Books;
import net.biaji.android.alephclient.data.DataContract.Shelf;
import net.biaji.android.alephclient.data.DataContract.ViewBooksInPocket;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * 以此类封装所有数据来源（网络/数据库）
 * 
 * @author biAji<biaji@biaji.net>
 */
public class DataFusionManager {

    private static DatabaseHelper datasource;

    private static DataFusionManager instance;

    private DataFusionManager() {
    }

    public static DataFusionManager getInstance(Context context) {
        if (instance == null) {
            instance = new DataFusionManager();
            datasource = new DatabaseHelper(context);
        }
        return instance;
    }

    /**
     * 获取指定书籍的当前存储状态
     * 
     * @param sn
     * @return
     */
    public int getBookStatus(String sn) {
        int result = Books.BOOK_NOT_SAVED;
        SQLiteDatabase db = datasource.getReadableDatabase();
        Cursor cur = db.query(Books.TABLE_NAME, new String[] {
                Books.COLUMN_NAME_STATUS
        }, Books.COLUMN_NAME_SN + " = ?", new String[] {
                sn
        }, null, null, null);

        if (cur.getCount() == 0)
            return result;

        cur.moveToFirst();
        result = cur.getInt(0);
        cur.close();
        return result;
    }

    /**
     * @param sn
     * @return
     */
    private long getBookId(String sn) {
        long result = -1L;
        SQLiteDatabase db = datasource.getReadableDatabase();
        Cursor cur = db.query(Books.TABLE_NAME, new String[] {
                DataContract._ID
        }, Books.COLUMN_NAME_SN + " = ?", new String[] {
                sn
        }, null, null, null);

        if (cur.getCount() == 0)
            return result;

        cur.moveToFirst();
        result = cur.getLong(0);
        cur.close();
        return result;
    }

    /**
     * 将书存入数据库
     * 
     * @param book
     * @param status
     * @return rowid
     */
    public long putBook(Book book, int status) {
        long rowId = getBookId(book.getSN());

        if (rowId == -1) {
            SQLiteDatabase db = datasource.getWritableDatabase();
            rowId = putBookInternal(book, status, db);
        } else {
            int bookStatus = getBookStatus(book.getSN());
            if (bookStatus == Books.BOOK_STATUS_SIMPLE) {
                updateBook(book);
            }
        }
        return rowId;
    }

    private long putBookInternal(Book book, int status, SQLiteDatabase db) {
        ContentValues cv = new ContentValues();
        cv.put(Books.COLUMN_NAME_SN, book.getSN());
        cv.put(Books.COLUMN_NAME_STANDARD_NUM, book.getStandardNum());
        cv.put(Books.COLUMN_NAME_ISBN, book.getISBN());
        cv.put(Books.COLUMN_NAME_NAME, book.getName());
        cv.put(Books.COLUMN_NAME_OTHER_NAME, book.getOtherName());
        cv.put(Books.COLUMN_NAME_AUTHORS, book.getAuthorStr());
        cv.put(Books.COLUMN_NAME_PUBLISHER, book.getPublisher());
        cv.put(Books.COLUMN_NAME_DESCRIBE, book.getDescribe());
        cv.put(Books.COLUMN_NAME_PRICE, book.getPrice());
        cv.put(Books.COLUMN_NAME_COVER_URL, book.getCoverUrl());
        cv.put(Books.COLUMN_NAME_MEDIA_TYPE, book.getMediaType());
        cv.put(Books.COLUMN_NAME_PUBLISH_DATE, book.getPubDate());
        cv.put(Books.COLUMN_NAME_STATUS, status);
        return db.insert(Books.TABLE_NAME, null, cv);
    }

    public void putBooks(ArrayList<Book> books) {
        SQLiteDatabase db = datasource.getWritableDatabase();
        for (Book book : books) {
            putBook(book, Books.BOOK_STATUS_SIMPLE);
        }
    }

    /**
     * 更新书籍信息
     * 
     * @param book
     */
    private void updateBook(Book book) {
        SQLiteDatabase db = datasource.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(Books.COLUMN_NAME_SN, book.getSN());
        cv.put(Books.COLUMN_NAME_STANDARD_NUM, book.getStandardNum());
        cv.put(Books.COLUMN_NAME_ISBN, book.getISBN());
        cv.put(Books.COLUMN_NAME_NAME, book.getName());
        cv.put(Books.COLUMN_NAME_OTHER_NAME, book.getOtherName());
        cv.put(Books.COLUMN_NAME_AUTHORS, book.getAuthorStr());
        cv.put(Books.COLUMN_NAME_PUBLISHER, book.getPublisher());
        cv.put(Books.COLUMN_NAME_DESCRIBE, book.getDescribe());
        cv.put(Books.COLUMN_NAME_PRICE, book.getPrice());
        cv.put(Books.COLUMN_NAME_COVER_URL, book.getCoverUrl());
        cv.put(Books.COLUMN_NAME_MEDIA_TYPE, book.getMediaType());
        cv.put(Books.COLUMN_NAME_PUBLISH_DATE, book.getPubDate());
        cv.put(Books.COLUMN_NAME_STATUS, Books.BOOK_STATUS_NORMAL);
        db.update(Books.TABLE_NAME, cv, Books.COLUMN_NAME_SN + " = ?", new String[] {
                book.getSN()
        });
    }

    /**
     * 获取指定书详情
     * 
     * @param sn
     * @return
     */
    public Book getBook(String sn) {
        Book result = null;
        SQLiteDatabase db = datasource.getReadableDatabase();
        Cursor cur = db.query(Books.TABLE_NAME, new String[] {
                DataContract._ID, Books.COLUMN_NAME_SN, Books.COLUMN_NAME_STANDARD_NUM,
                Books.COLUMN_NAME_ISBN, Books.COLUMN_NAME_NAME, Books.COLUMN_NAME_OTHER_NAME,
                Books.COLUMN_NAME_AUTHORS, Books.COLUMN_NAME_PUBLISHER, Books.COLUMN_NAME_DESCRIBE,
                Books.COLUMN_NAME_PRICE, Books.COLUMN_NAME_COVER_URL, Books.COLUMN_NAME_MEDIA_TYPE,
                Books.COLUMN_NAME_PUBLISH_DATE, Books.COLUMN_NAME_STATUS
        }, Books.COLUMN_NAME_SN + " = ?", new String[] {
                sn
        }, null, null, null);

        if (cur.getCount() == 0)
            return result;
        result = new Book();
        cur.moveToFirst();
        result.setSN(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_SN)));
        result.setName(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_NAME)));
        result.setAuthors(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_AUTHORS)));
        result.setDescribe(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_DESCRIBE)));
        result.setISBN(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_ISBN)));
        result.setStandardNum(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_STANDARD_NUM)));
        result.setCoverUrl(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_COVER_URL)));
        result.setPrice(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_PRICE)));
        result.setPubDate(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_PUBLISH_DATE)));
        result.setPublisher(cur.getString(cur.getColumnIndex(Books.COLUMN_NAME_PUBLISHER)));
        cur.close();
        return result;
    }

    /**
     * 获取在借图书信息
     * 
     * @return
     */
    public ArrayList<Book> getBookInPocket() {
        ArrayList<Book> bookList = new ArrayList<Book>();
        SQLiteDatabase db = datasource.getWritableDatabase();

        Cursor cursor = db.query(ViewBooksInPocket.VIEW_NAME, new String[] {
                "*"
        }, null, null, null,
                null, null);
        if (cursor.getCount() == 0)
            return bookList;

        while (cursor.moveToNext()) {
            Book book = new Book();
            book.setName(cursor.getString(cursor.getColumnIndex(Books.COLUMN_NAME_NAME)));
            book.setAuthors(cursor.getString(cursor.getColumnIndex(Books.COLUMN_NAME_AUTHORS)));
            book.setBorrowDate(new Date(cursor.getLong(cursor
                    .getColumnIndex(Shelf.COLUMN_NAME_BORROW_DATE))));
            book.setReturnDateExpected(new Date(cursor.getLong(cursor
                    .getColumnIndex(Shelf.COLUMN_NAME_RETURN_DATE_EXPECTED))));
            bookList.add(book);
        }
        cursor.close();
        return bookList;
    }

    /**
     * 更新在借图书列表<br>
     * TODO:还书后置状态为曾经借阅
     * 
     * @param booksInPocket 在借图书列表
     */
    public void updateBorrowInfo(ArrayList<Book> booksInPocket) {

        SQLiteDatabase db = datasource.getWritableDatabase();
        String cleanSql = "UPDATE " + Shelf.TABLE_NAME + " SET status=" + Shelf.STATUS_HISTORY
                + " WHERE status=" + Shelf.STATUS_INHAND;
        db.execSQL(cleanSql);
        for (Book book : booksInPocket) {
            long rowId = putBook(book, Books.BOOK_STATUS_SIMPLE);
            int status = getBookInPocketStatus(rowId);
            if (status == Shelf.STATUS_NOT_HERE) {
                ContentValues cv = new ContentValues();
                cv.put(Shelf.COLUMN_NAME_STATUS, Shelf.STATUS_INHAND);
                cv.put(Shelf.COLUMN_NAME_BOOKID, rowId);
                cv.put(Shelf.COLUMN_NAME_RETURN_DATE_EXPECTED, book.getReturnDateExpected()
                        .getTime());
                cv.put(Shelf.COLUMN_NAME_ADD_DATE, System.currentTimeMillis());
                db.insert(Shelf.TABLE_NAME, null, cv);
            } else if (status != DataContract.Shelf.STATUS_INHAND) {
                ContentValues cv = new ContentValues();
                cv.put(Shelf.COLUMN_NAME_STATUS, Shelf.STATUS_INHAND);
                cv.put(Shelf.COLUMN_NAME_RETURN_DATE_EXPECTED, book.getReturnDateExpected()
                        .getTime());
                db.update(Shelf.TABLE_NAME, cv, Shelf.COLUMN_NAME_BOOKID + "=?", new String[] {
                        "" + rowId
                });
            }

        }
    }

    /**
     * 获取指定图书的状态
     * 
     * @param rowId 图书id
     * @return 图书状态
     */
    private int getBookInPocketStatus(long rowId) {
        int result = -1;
        SQLiteDatabase db = datasource.getReadableDatabase();
        Cursor cur = db.query(DataContract.Shelf.TABLE_NAME, new String[] {
                DataContract.Shelf.COLUMN_NAME_STATUS
        }, DataContract.Shelf.COLUMN_NAME_BOOKID + " = ?", new String[] {
                rowId + ""
        }, null, null, null);

        if (cur.getCount() == 0)
            return result;

        cur.moveToFirst();
        result = cur.getInt(0);
        cur.close();
        return result;
    }
}
